import javax.servlet.http.*;
import java.sql.*;
import java.io.*;

public class StudentLoginServlet extends HttpServlet
{

String student_name;
             String student_id;
             String college_code;         
             String course;  

             String ssc;
             String ssc_year;
             String inter;         
             String inter_year;     
             String graduation;
             String graduation_year;
             String pg;         
             String pg_year; 
             String student_uname;
             String technical_skills;
             String mobile;
             String email;
            
  
  public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException
  {

    
 
        String student_uname=request.getParameter("student_uname");
        String student_pwd=request.getParameter("student_pwd");

         response.setContentType("text/html");
         PrintWriter out=response.getWriter();

          try
       {
        Class.forName("oracle.jdbc.driver.OracleDriver");

         Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","pac","mca"); 

         Statement stmt=con.createStatement();
         Statement stmt2=con.createStatement();
         Statement stmt3=con.createStatement();
         
		String str=null;
         ResultSet rs = stmt.executeQuery("SELECT *FROM student_login WHERE student_uname='"+student_uname+"' and student_pwd='"+student_pwd+"'");
		          
         if(rs.next())

         {
                      
        ResultSet rs2=stmt.executeQuery("SELECT *FROM student_register WHERE student_uname='"+student_uname+"'");

        if(rs2.next())
        {
            student_name = rs2.getString(1);
            student_id = rs2.getString(2);
            college_code = rs2.getString(3);         
            course = rs2.getString(4);  

      }
        
           
          ResultSet rs3=stmt.executeQuery("SELECT *FROM student_marks WHERE student_uname='"+student_uname+"'");

        if(rs3.next())
        {
            ssc = rs3.getString(1);
            ssc_year = rs3.getString(2);
            inter = rs3.getString(3);         
            inter_year = rs3.getString(4);     
            graduation = rs3.getString(5);
            graduation_year = rs3.getString(6);
            pg = rs3.getString(7);         
            pg_year = rs3.getString(8); 
            student_id=rs3.getString(9);
            student_uname=rs3.getString(10);
            technical_skills=rs3.getString(11);
                             
        }
       
            ResultSet rs7=stmt2.executeQuery("SELECT student_mobile,student_email from student_details WHERE student_id='"+student_id+"'");
            if(rs7.next())
        {
           mobile = rs7.getString("student_mobile");
           
           email = rs7.getString("student_email");
           
       }
ResultSet rs5 = stmt3.executeQuery("SELECT company_name from company_register");
 
while(rs5.next())
		  {
		  String s=rs5.getString("company_name");
		  str=str+"<option value='"+s+"'>"+s+"</option>";

		  }
		 
        out.println("<html><head><script src='show.js'></script></head> ");
      out.println("<body background=\"t11.jpg\" onload='showCompany()'>"); 
out.println("<div><div style='float:left'>");
out.println("<form action='index.html'>");
 out.println("<input type='submit' value='home'/></form></div>");
out.println("<div style='float:right'>");
 out.println("<form action='student-login.html'>");
            out.println("<input type='submit'  value='logout'/></form></div>");
out.println("</div>");

out.println("<div style='float:right;width:45%'>");
            out.println("<form method='post' action='ssc'>");
            out.println(" <font color=\"white\">Select company name </font><select name='company' id='company' ><option value='--select--'>--select--</option>"+str+"</select>  <input type='submit' value='search'/></form>"); 
         out.println("</div>");
out.println("<br><h1> <font color=\"white\"> "+student_name+" </font> </h1> ");
     out.println("<br> <br> <br> ");

     out.println("<table  cellspacing='3'width='50%'>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> Id  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+student_id+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> College Code  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+college_code+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> Course  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+course+" </font> </b>");
        out.println("</tr>");   
     
        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> SSC Marks Percentage </font>  </b>");
              out.println("<td> <b> <font color=\"white\"> :"+ssc+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> Year of Passing  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+ssc_year+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\">Intermediate Marks Percentage </font>  </b>");
              out.println("<td> <b> <font color=\"white\"> :"+inter+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\">Year of Passing  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+inter_year+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\">Degree Marks Percentage  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+graduation+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\">Year of Passing </font>  </b>");
              out.println("<td> <b> <font color=\"white\"> :"+graduation_year+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> PG Marks Percentage </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+pg+" </font> </b>");
        out.println("</tr>");

        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> Year of Passing  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+pg_year+" </font> </b>");
              //out.println("<td><b><input type='text' value='"+pg_year+"'/>");
        out.println("</tr>");
        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> technical_skills </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+technical_skills+" </font> </b>");
              //out.println("<td><b><input type='text' value='"+pg_year+"'/>");
        out.println("</tr>");
        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> email  </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+email+" </font> </b>");
              //out.println("<td><b><input type='text' value='"+pg_year+"'/>");
        out.println("</tr>");
        out.println("<tr>");
              out.println(" <td> <b> <font color=\"black\"> mobile </font> </b>");
              out.println("<td> <b> <font color=\"white\"> :"+mobile+" </font> </b>");
              //out.println("<td><b><input type='text' value='"+pg_year+"'/>");
        out.println("</tr>");

     out.println(" </table> ");
     out.println("<br><form method='post' action='edit.jsp'>");
     out.println("<input type='hidden' name='student_id' value='"+student_id+"'/>");
     out.println("<input type='submit' value='edit'/></form>");
      out.println("<br><br><blink><b><font color='#ffa639' size='6'/>UPDATES</b></blink>");
      ResultSet rs9=stmt.executeQuery("select college_name from imp_stu where student_id='"+student_id+"'");
         out.println("<center><table border='1'width='80%'bgcolor='#e0ccff' >");
         
         while(rs9.next())
		  {
		  String college_code=rs9.getString("college_name");
		  
      out.println("<tr><td>");
       
         out.println("<font color='black' size='5'><center>");
        
        
          out.println(college_code+"</center>");
          out.println("</td><td>");
           out.println("<font color='black'size='5'><center> Flll the form :");
         out.println("</center></font></td><td>");
        
     out.println("<form action='fill.jsp' method='post' >"+"<input type='hidden' name='student_id' value='"+student_id+"'>");
     out.println("<input type='submit' value='open'/></form></center></td></tr>");
         }
     out.println("</table>");
             ResultSet rs6=stmt.executeQuery("select college_code from message_stu where student_id='"+student_id+"'");
         out.println("<center><table border='1'width='80%'bgcolor='#e0ccff' >");
         
         while(rs6.next())
		  {
		  String college_code=rs6.getString("college_code");
		  
      out.println("<tr><td>");
       
         out.println("<font color='black' size='5'><center>");
        
        
          out.println(college_code+"</center>");
          out.println("</td><td>");
           out.println("<font color='black'size='5'><center> registraions are open :");
         out.println("</center></font></td><td>");
        
     out.println("<form action='sm' method='post' >"+"<input type='hidden'  name='student_id' value='"+student_id+"'>");
     out.println("<input type='submit' value='checkhere'/></form></center></td></tr>");
     
     out.println("</body>");

     out.println("</html>");   
         }
 }
         
      else


       {

           out.println("<html>");
          out.println("<head>");
           

     out.println("<STYLE Type='text/css'>" +
             ".chk:focus{" +
             " background-color:brown;" +
            "} </style></head>");   
     
           out.println("<body background=\"t5.jpg\">");

           out.println(" <font color=\"white\" size=\"4\"> ");
        
        out.println("<body background='t5.jpg'>");
   out.println ("<div style='float:left'>");
out.println("<form action='index.html'>");
   out.println(" <input type='submit' value='home'/></form></div>");
   
   out.println("<br><br><br>");
   out.println("<center> <h2> <font color='white'> Student Login </font> </h2> </center>");
             
out.println("<center>");

out.println("<form action='sls2' method='post'>");


out.println("<br><br><br><br>");

out.println("<table width=350>");
 
             
  
  out.println("<tr><td></td> </tr>");

  out.println("<tr> <td><b><font color='white'> User Name: </font><b></td> ");
out.println("<td></td><td><input class=chk type='text' name='student_uname'/></td></tr>");
   out.println("<tr> <td><b><font color='white'>Password :  </font><b></td><td></td>");
          out.println("<td><input class=chk type='password' name='student_pwd'></td></tr>");


out.println("<table>");


out.println("<br><br>");

out.println("<center>  <input type='submit' value='Login'/> </center>");
            

out.println("<br><br>");
 out.println("<br><br> <b>  Invalid Username/password </b>");
out.println("<center> <h2><font color='blue'> Not a member, </font> <a href='studentdetails.jsp'><font color='cyan'>Register here</font> </a>  <h2></center>");

 out.println("</form>");


           out.println("<br><br> <b> reenter the Correct Username and Password </b>");

           out.println(" </font> ");
    
           out.println("</body>");

           out.println("</html>");   

        }       

     }
 
    catch(Exception e)
       {
        // response.sendError(505,e.getMessage());
         e.printStackTrace();
       }    

  }

}  

 